﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using LibrarySolution.Model.基础;
using LibrarySolution.DB;

namespace LibrarySolution.DAO.基础
{
    class JHBkDao
    {
        String sql;
        DataTable dt;
        List<SqlParameter> list;
        private void tsQuery(JSRecord jsRecord)
        {
            list = new List<SqlParameter>();
            //sql = "select bk_code as 书本编码,bk_no as 书本编号,bk_name as 书本名称,bk_writer as 作者,bk_publish as 出版社," +
            //    "bk_state as 状态,bk_price as 价格,bk_cbsj as 出版时间 from jc_bkxx where bk_state = '在库'";
            sql = "select bk_no as 书本编号,bk_name as 书本名称,bk_writer as 作者,bk_publish as 出版社," +
                "bk_cbsj as 出版时间 from jc_bkxx where bk_state = '在库'";
            if (jsRecord.js_tsmc != null)
            {
                list.Add(new SqlParameter("@jsTsmc",jsRecord.js_tsmc));
                sql += " and ( bk_name like '%' + @jsTsmc + '%')";
            }
            if (jsRecord.js_writer != null)
            {
                list.Add(new SqlParameter("@jsWriter", jsRecord.js_writer));
                sql += " and (bk_writer like '%' + @jsWriter + '%')";
            }
            if (jsRecord.js_tsbh != null)
            {
                list.Add(new SqlParameter("@jsBkno", jsRecord.js_tsbh));
                sql += " and (bk_no like '%' + @jsBkno + '%')";
            }
        }

        public DataTable getTsjl(JSRecord jsRecord)
        {
            tsRecord(jsRecord);
            dt = DBExecuteUtil.querySqlGetTable(sql, list.ToArray());
            return dt;
        }

        //public DataTable getDsjl(JSRecord jsEntity)
        //{
        //    sql = "select bk_no as 书本编号,bk_name as 书本名称,js_time as 借书日期," +
        //        "yh_name as 用户名称 from jc_bkxx, jc_yhxx,jc_jsjl where bk_no = js_tsbm " +
        //        "and yh_no = js_yhno and bk_state='外借' and js_sfyh = 'False' and js_dskf = 0 and js_yhno=@jsYhno";
        //    SqlParameter[] parameters = { new SqlParameter("@jsYhno", jsEntity.js_yhno) };
        //    dt = DBExecuteUtil.querySqlGetTable(sql, parameters);
        //    return dt;
        //}

        //public DataTable getCqBkJl(JSRecord jsEntity)
        //{
        //    sql = "select js_sfyh as 是否已还,bk_no as 书本编号,bk_name as 书本名称,js_ghrq as 归还日期,"+
        //        "js_shrq as 实还日期,js_sfkf as 是否扣费 from jc_bkxx,jc_yhxx,jc_jsjl where "+
        //        "bk_no = js_tsbm  and yh_no = js_yhno and js_sfyh='false' and bk_state='外借' and js_yhno=@jsYhno";
        //    SqlParameter[] parameters = { new SqlParameter("@jsYhno", jsEntity.js_yhno) };
        //    dt = DBExecuteUtil.querySqlGetTable(sql, parameters);
        //    return dt;
        //}

        public DataTable getDsBkJl(JSRecord jsEntity)
        {
            sql = "select bk_no as 书本编号,bk_name as 书本名称,js_time as 借书日期,bk_price as 书目价格 from "+
                "jc_bkxx,jc_jsjl where bk_no=js_tsbm and js_dskf='0' and js_sfyh='0' and bk_state='丢失' and js_yhno=@jsYhno";
            SqlParameter[] parameters = { new SqlParameter("@jsYhno", jsEntity.js_yhno) };
            dt = DBExecuteUtil.querySqlGetTable(sql, parameters);
            return dt;
        }

        public DataTable getGhJl(JSRecord jsEntity)
        {
            sql = "select bk_no as 书本编号,bk_name as 书本名称,js_time as 借书日期,yh_name as 用户名称," +
                "js_code as 记录编码,js_yhno as 用户编号 from jc_bkxx, jc_yhxx,jc_jsjl where bk_no = js_tsbm " +
                "and yh_no = js_yhno and bk_state='外借' and js_sfyh = '0' and js_yhno=@jsYhno";
            SqlParameter[] parameters = { new SqlParameter("@jsYhno", jsEntity.js_yhno) };
            dt = DBExecuteUtil.querySqlGetTable(sql,parameters);
            return dt;
        }

        //public DataTable getJyjl(String yhNo)
        //{
        //    sql = "select js_sfyh as 是否已还,js_code as 记录编号, bk_no as 书本编号,bk_name as 书本名称," +
        //        "js_yhno as 用户编号,js_time as 借书日期,yh_name as 用户姓名,js_tsbm as 图书编码,js_ghrq as 归还日期," +
        //        "js_shrq as 实还日期,js_sfkf as 是否扣费,js_dskf as 丢失扣费 from jc_bkxx, jc_yhxx,jc_jsjl where bk_no =" +
        //        " js_tsbm and yh_no = js_yhno and js_sfyh='False' and bk_state='外借' and js_yhno=@yhNo";
        //    SqlParameter[] parameters = { new SqlParameter("@yhNo", yhNo) };
        //    dt = DBExecuteUtil.querySqlGetTable(sql,parameters);
        //    return dt;
        //}

        private void tsRecord(JSRecord jsRecord)
        {
            list = new List<SqlParameter>();
            sql = "select js_sfyh as 是否已还,js_code as 记录编号, bk_no as 书本编号,bk_name as 书本名称," +
                "js_yhno as 用户编号,js_time as 借书日期,yh_name as 用户姓名,js_tsbm as 图书编码,js_ghrq as 归还日期," +
                "js_shrq as 实还日期,js_sfkf as 是否扣费,js_dskf as 丢失扣费,bk_state as 图书状态 from jc_bkxx, jc_yhxx,jc_jsjl where bk_no ="+
                " js_tsbm and yh_no = js_yhno";
            if (jsRecord.js_tsbh != null)
            {
                list.Add(new SqlParameter("@Tsbh", jsRecord.js_tsbh));
                sql += " and bk_no like '%' + @Tsbh + '%'";
            }
            if (jsRecord.js_yhno != null)
            {
                list.Add(new SqlParameter("@Yhno", jsRecord.js_yhno));
                sql += " and js_yhno = @Yhno";
            }
            if (jsRecord.js_tsmc != null)
            {
                list.Add(new SqlParameter("@Tsmc", jsRecord.js_tsmc));
                sql += " and bk_name like '%' + @Tsmc + '%'";
            }
            if (jsRecord.js_state == "false")
            {
                sql += " and js_sfyh = '0'";
            }
            else if (jsRecord.js_state == "true")
            {
                sql += " and js_sfyh = '1'";
            }
            sql += " order by js_code";
        }

        public DataTable getMybook(JSRecord jsRecord)
        {
            addSqlparas(jsRecord);
            sql = "select bk_no as 图书编号,bk_name as 图书名称,js_time as 借书时间,js_ghrq as 归还日期,js_code as 记录编码 " +
                "from jc_jsjl,jc_bkxx where js_tsbm = bk_no and js_sfyh = '0'and bk_state='外借' and bk_yhno = @Yhno";
            dt = DBExecuteUtil.querySqlGetTable(sql, list.ToArray());
            return dt;
        }

        public DataTable getBkxxTable(JSRecord jsRecord)
        {
            tsQuery(jsRecord);
            dt = DBExecuteUtil.querySqlGetTable(sql, list.ToArray());
            return dt;
        }

        public String getBkCount()
        {
            String result;
            sql = "select count(*) from jc_bkxx where bk_state = '在库'";
            result = DBExecuteUtil.querySql(sql, null);
            return result;
        }

        public DataTable getBkTbByQuery(JSRecord jsRecord)
        {
            tsQuery(jsRecord);
            dt = DBExecuteUtil.querySqlGetTable(sql, list.ToArray());
            return dt;
        }

        public void updateXjxx(JSRecord jsRecord)
        {
            if (jsRecord.js_yhno != null)
                list.Add(new SqlParameter("@Yhno", int.Parse(jsRecord.js_yhno)));
            jsRecord.js_yhxj = Convert.ToString(int.Parse(getYhXjsl(jsRecord)) + 1);
            addSqlparas(jsRecord);
            sql = "update jc_jsjl set js_ghrq=@Ghrq where js_code=@jsCode";
            DBExecuteUtil.querySqlV(sql, list.ToArray());
            sql = "update jc_yhxx set yh_xjsl=@Xjsl where yh_no = @Yhno";
            DBExecuteUtil.querySqlV(sql, list.ToArray());
        }

        public void insertJs(JSRecord jsRecord)
        {
            addSqlparas(jsRecord);
            sql = "insert into jc_jsjl (js_tsbm,js_yhno,js_time,js_sfyh,js_ghrq,js_sfkf)" +
                " values(@bkNo,@Yhno,@Time, '0',@Ghrq,0)";
            DBExecuteUtil.querySqlV(sql, list.ToArray());
            updateYhxx(jsRecord);
            updateBkxx(jsRecord);
        }

        public void changeJs(JSRecord jsRecord)
        {
            addSqlparas(jsRecord);
            sql = "update jc_jsjl set js_sfyh = '1', js_shrq=@Shrq where js_code = @jsCode and js_tsbm = @bkNo";
            DBExecuteUtil.querySqlV(sql, list.ToArray());
            updateYhxxH(jsRecord);
            updateBkxxH(jsRecord);
        }

        private void updateYhxx(JSRecord jsRecord)
        {
            jsRecord.zjsl = getYhZjsl();
            addSqlparas(jsRecord);
            sql = "update jc_yhxx set yh_yjsl = @Yjsl,yh_xjsl = @Xjsl,yh_zjsl=@Zjsl where yh_no = @Yhno";
            DBExecuteUtil.querySqlV(sql, list.ToArray());
        }

        private void updateBkxx(JSRecord jsRecord)
        {
            addSqlparas(jsRecord);
            list.Add(new SqlParameter("@bkBjcs", getBkBjcs()));
            sql = "update jc_bkxx set bk_state = '外借', bk_yhno = @Yhno,bk_bjcs = @bkBjcs where bk_no = @bkNo ";
            DBExecuteUtil.querySqlV(sql, list.ToArray());
        }

        private string getBkBjcs()
        {
            string result;
            sql = "select bk_bjcs from jc_bkxx where bk_no=@bkNo";
            result =DBExecuteUtil.querySql(sql, list.ToArray());
            return Convert.ToString(int.Parse(result) + 1);
        }

        private void updateBkxxH(JSRecord jsRecord)
        {
            addSqlparas(jsRecord);
            sql = "update jc_bkxx set bk_state = '在库', bk_yhno = 0 where bk_No = @bkNo";
            DBExecuteUtil.querySqlV(sql, list.ToArray());
        }

        private void updateYhxxH(JSRecord jsRecord)
        {
            String yhGhsl = (int.Parse(getYhGhsl()) + 1).ToString();
            jsRecord.js_yhyj = getYhYjsl(jsRecord);
            //jsRecord.js_yhxj = Convert.ToString(int.Parse(getYhXjsl(jsRecord)) - 1);
            addSqlparas(jsRecord);
            list.Add(new SqlParameter("@yhGhsl", yhGhsl));
            sql = "update jc_yhxx set yh_yjsl = @Yjsl,yh_ghsl=@yhGhsl where yh_no = @Yhno";
            DBExecuteUtil.querySqlV(sql, list.ToArray());
        }

        private String getYhZjsl()
        {
            String result;
            sql = "select yh_zjsl from jc_yhxx where yh_no=@yhNo";
            result = DBExecuteUtil.querySql(sql, list.ToArray());
            return Convert.ToString(int.Parse(result) + 1);
        }

        private String getYhGhsl()
        {
            String result;
            sql = "select yh_ghsl from jc_yhxx where yh_no=@yhNo";
            result = DBExecuteUtil.querySql(sql, list.ToArray());
            return result;
        }

        private String getYhYjsl(JSRecord jsRecord)
        {
            String result;
            addSqlparas(jsRecord);
            sql = "select yh_yjsl from jc_yhxx where yh_no = @Yhno";
            result = DBExecuteUtil.querySql(sql, list.ToArray());
            return Convert.ToString(int.Parse(result) - 1);
        }

        private String getYhXjsl(JSRecord jsRecord)
        {
            String result;
            addSqlparas(jsRecord);
            sql = "select yh_xjsl from jc_yhxx where yh_no = @Yhno";
            result = DBExecuteUtil.querySql(sql, list.ToArray());
            return result;
        }

        private void addSqlparas(JSRecord jsRecord)
        {
            list = new List<SqlParameter>();
            if (jsRecord.js_code != null)
                list.Add(new SqlParameter("@jsCode", jsRecord.js_code));
            if (jsRecord.js_yhno != null)
                list.Add(new SqlParameter("@Yhno", int.Parse(jsRecord.js_yhno)));
            if (jsRecord.js_tsbm != null)    
                list.Add(new SqlParameter("@Tsbm", jsRecord.js_tsbm));
            if (jsRecord.js_time != null)    
                list.Add(new SqlParameter("@Time", jsRecord.js_time));
            if (jsRecord.js_yhyj != null)
                list.Add(new SqlParameter("@Yjsl", jsRecord.js_yhyj));
            if (jsRecord.js_yhxj != null)
                list.Add(new SqlParameter("@Xjsl", jsRecord.js_yhxj));
            if (jsRecord.js_tsbh != null)
                list.Add(new SqlParameter("@bkNo", jsRecord.js_tsbh));
            if (jsRecord.js_ghrq != null)
                list.Add(new SqlParameter("@Ghrq", jsRecord.js_ghrq));
            if (jsRecord.zjsl != null)
                list.Add(new SqlParameter("@Zjsl", jsRecord.zjsl));
            if (jsRecord.js_shrq != null)
                list.Add(new SqlParameter("@Shrq", jsRecord.js_shrq));
        }
    }
}
